﻿SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Blog_Tags]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Blog_Tags](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[ParentId] [int] NULL,
	[CreationDate] [datetime] NOT NULL CONSTRAINT [DF_BlogTags_CreationDate]  DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL,
 CONSTRAINT [PK_BlogTags] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Blog_Blogs]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Blog_Blogs](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Title] [nvarchar](50) NOT NULL,
	[CreationDate] [datetime] NOT NULL CONSTRAINT [DF_Blogs_CreationDate]  DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Blogs] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Blog_Authors]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Blog_Authors](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Email] [nvarchar](100) NOT NULL,
	[Username] [nvarchar](50) NOT NULL,
	[Password] [nvarchar](64) NOT NULL,
	[CreationDate] [datetime] NOT NULL CONSTRAINT [DF_BlogUsers_CreationDate]  DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL,
 CONSTRAINT [PK_BlogUsers] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Blog_Comments]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Blog_Comments](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Content] [nvarchar](2000) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Homepage] [nvarchar](200) NULL,
	[Email] [nvarchar](100) NULL,
	[IpAddress] [nvarchar](15) NOT NULL,
	[PostId] [int] NOT NULL,
	[CreationDate] [datetime] NOT NULL CONSTRAINT [DF_BlogComments_CreationDate]  DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL,
 CONSTRAINT [PK_BlogComments] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Blog_PostTagging]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Blog_PostTagging](
	[PostId] [int] NOT NULL,
	[TagId] [int] NOT NULL,
 CONSTRAINT [PK_BlogPostTagging] PRIMARY KEY CLUSTERED 
(
	[PostId] ASC,
	[TagId] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Blog_Posts]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Blog_Posts](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Title] [nvarchar](200) NOT NULL,
	[Description] [nvarchar](1000) NOT NULL,
	[Content] [nvarchar](max) NOT NULL,
	[BlogId] [int] NOT NULL,
	[AuthorId] [int] NOT NULL,
	[CreationDate] [datetime] NOT NULL CONSTRAINT [DF_BlogPosts_CreationDate]  DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL,
	[Permalink] [nvarchar](200) NOT NULL,
 CONSTRAINT [PK_BlogPosts] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'[dbo].[Blog_Posts]') AND name = N'IX_Blog_Posts_Permalink')
CREATE UNIQUE NONCLUSTERED INDEX [IX_Blog_Posts_Permalink] ON [dbo].[Blog_Posts] 
(
	[Permalink] ASC
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Blog_BlogAuthors]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Blog_BlogAuthors](
	[AuthorId] [int] NOT NULL,
	[BlogId] [int] NOT NULL,
 CONSTRAINT [PK_BlogWriters] PRIMARY KEY CLUSTERED 
(
	[AuthorId] ASC,
	[BlogId] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_BlogTags_BlogTags]') AND type = 'F')
ALTER TABLE [dbo].[Blog_Tags]  WITH CHECK ADD  CONSTRAINT [FK_BlogTags_BlogTags] FOREIGN KEY([ParentId])
REFERENCES [dbo].[Blog_Tags] ([Id])
GO
ALTER TABLE [dbo].[Blog_Tags] CHECK CONSTRAINT [FK_BlogTags_BlogTags]
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_BlogComments_BlogPosts]') AND type = 'F')
ALTER TABLE [dbo].[Blog_Comments]  WITH CHECK ADD  CONSTRAINT [FK_BlogComments_BlogPosts] FOREIGN KEY([PostId])
REFERENCES [dbo].[Blog_Posts] ([Id])
GO
ALTER TABLE [dbo].[Blog_Comments] CHECK CONSTRAINT [FK_BlogComments_BlogPosts]
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_BlogPostTagging_BlogPosts]') AND type = 'F')
ALTER TABLE [dbo].[Blog_PostTagging]  WITH CHECK ADD  CONSTRAINT [FK_BlogPostTagging_BlogPosts] FOREIGN KEY([PostId])
REFERENCES [dbo].[Blog_Posts] ([Id])
GO
ALTER TABLE [dbo].[Blog_PostTagging] CHECK CONSTRAINT [FK_BlogPostTagging_BlogPosts]
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_BlogPostTagging_BlogTags]') AND type = 'F')
ALTER TABLE [dbo].[Blog_PostTagging]  WITH CHECK ADD  CONSTRAINT [FK_BlogPostTagging_BlogTags] FOREIGN KEY([TagId])
REFERENCES [dbo].[Blog_Tags] ([Id])
GO
ALTER TABLE [dbo].[Blog_PostTagging] CHECK CONSTRAINT [FK_BlogPostTagging_BlogTags]
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_BlogPosts_Blogs]') AND type = 'F')
ALTER TABLE [dbo].[Blog_Posts]  WITH CHECK ADD  CONSTRAINT [FK_BlogPosts_Blogs] FOREIGN KEY([BlogId])
REFERENCES [dbo].[Blog_Blogs] ([Id])
GO
ALTER TABLE [dbo].[Blog_Posts] CHECK CONSTRAINT [FK_BlogPosts_Blogs]
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_BlogPosts_BlogUsers]') AND type = 'F')
ALTER TABLE [dbo].[Blog_Posts]  WITH CHECK ADD  CONSTRAINT [FK_BlogPosts_BlogUsers] FOREIGN KEY([AuthorId])
REFERENCES [dbo].[Blog_Authors] ([Id])
GO
ALTER TABLE [dbo].[Blog_Posts] CHECK CONSTRAINT [FK_BlogPosts_BlogUsers]
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_BlogWriters_Blogs]') AND type = 'F')
ALTER TABLE [dbo].[Blog_BlogAuthors]  WITH CHECK ADD  CONSTRAINT [FK_BlogWriters_Blogs] FOREIGN KEY([BlogId])
REFERENCES [dbo].[Blog_Blogs] ([Id])
GO
ALTER TABLE [dbo].[Blog_BlogAuthors] CHECK CONSTRAINT [FK_BlogWriters_Blogs]
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_BlogWriters_BlogUsers]') AND type = 'F')
ALTER TABLE [dbo].[Blog_BlogAuthors]  WITH CHECK ADD  CONSTRAINT [FK_BlogWriters_BlogUsers] FOREIGN KEY([AuthorId])
REFERENCES [dbo].[Blog_Authors] ([Id])
GO
ALTER TABLE [dbo].[Blog_BlogAuthors] CHECK CONSTRAINT [FK_BlogWriters_BlogUsers]
